﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.IO;
using System.Windows.Forms;

namespace CSLAGenTool
{
	public class Data
	{
		private SqlConnection cnn;
		private string strcon;
		private SqlDataReader dataReader = null;

		// Constructor
		public Data(string path)
		{
			string[] sTmp = File.ReadAllLines(path + "config.txt");
			strcon = sTmp[0];
			string s = OpenConnection();
		}

		// Mở CSDL
		public string OpenConnection()
		{
			try
			{
				cnn = new SqlConnection(strcon);
				cnn.Open();
				return "";
			}
			catch (SqlException eq)
			{
				return "Lỗi: " + eq.Message + "\r\n" + eq.TargetSite;
			}
			catch (Exception ex)
			{
				return "Lỗi: " + ex.Message + "\r\n" + ex.TargetSite;
			}
		}

		// Đóng CSDL
		public void CloseConnection()
		{
			if (cnn.State != ConnectionState.Closed)
			{
				cnn.Close();
				cnn.Dispose();
			}
		}

		// Thực thi query
		public SqlDataReader Exec(string scmd)
		{
			if (dataReader != null && !dataReader.IsClosed)
				dataReader.Close();
			dataReader = null;
			try
			{
				SqlCommand sqlComm = new SqlCommand(scmd, cnn);
				dataReader = sqlComm.ExecuteReader();
			}
			catch
			{
				dataReader = null;
			}
			return dataReader;
		}

		//Thực thi lệnh
		public string ExecNonQuery(string scmd)
		{
			try
			{
				SqlCommand sqlComm = new SqlCommand(scmd, cnn);
				int i = sqlComm.ExecuteNonQuery();
				return "";
			}
			catch (Exception ex)
			{
				return "Lỗi: " + ex.Message + "\r\n" + ex.TargetSite;
			}
		}

		//Lấy toàn bộ thông tin của table
		public TableInfo GetTableInfo(string tableName)
		{
			string defaultValue = null;

			TableInfo tableInfo = new TableInfo();
			tableInfo.tableName = tableName;

			string primaryKey = GetPrimaryKey(tableName);

			string scmd = "select * from information_schema.columns where table_name='" + tableName + "' ORDER BY ORDINAL_POSITION";
			dataReader = this.Exec(scmd);
			if (dataReader != null)
				if (dataReader.HasRows)
					while (dataReader.Read())
					{
						ColumnInfo columnInfo = new ColumnInfo();
						columnInfo.columnName = (string)dataReader["COLUMN_NAME"];
						columnInfo.isNullable = (string)dataReader["IS_NULLABLE"] == "NO" ? false : true;
						columnInfo.description = GetColumnDescription(tableName, columnInfo.columnName);
						columnInfo.stringLength = (dataReader["CHARACTER_MAXIMUM_LENGTH"] is DBNull) ? -1 : int.Parse(dataReader["CHARACTER_MAXIMUM_LENGTH"].ToString());
						if (!(dataReader["NUMERIC_PRECISION"] is DBNull) && !(dataReader["NUMERIC_SCALE"] is DBNull))
							columnInfo.decimalLength = "(" + dataReader["NUMERIC_PRECISION"].ToString() + "," + dataReader["NUMERIC_SCALE"].ToString() + ")";
						columnInfo.variantType = convert2VariantType((string)dataReader["DATA_TYPE"]);
						columnInfo.sqlDataType = convert2SqlDataType((string)dataReader["DATA_TYPE"]);
						columnInfo.isPrimaryKey = (primaryKey == columnInfo.columnName);
						defaultValue = (dataReader["COLUMN_DEFAULT"] is DBNull) ? null : (string)dataReader["COLUMN_DEFAULT"];
						columnInfo.defaultValue = AnalyseDefaultValue(defaultValue, (string)dataReader["DATA_TYPE"]);
						columnInfo.isAutoID = ChkColumnAutoID(tableName, columnInfo.columnName);
						tableInfo.columnInfos.Add(columnInfo);
						//
						if (columnInfo.isPrimaryKey)
						{
							tableInfo.primaryKey = columnInfo.columnName;
							tableInfo.isAutoID = columnInfo.isAutoID;
							tableInfo.variantType = columnInfo.variantType;
							tableInfo.sqlDataType = columnInfo.sqlDataType;
							tableInfo.intLength = (tableInfo.sqlDataType == "BigInt" ? 19 : 10);
						}
					}

			return tableInfo;
		}

		//Lấy các table trong batabase
		public string[] GetAllTableName()
		{
			string scmd = "select * from sys.tables";
			dataReader = this.Exec(scmd);

			ArrayList arr = new ArrayList();
			while (dataReader.Read())
				arr.Add(dataReader.GetValue(0).ToString());

			string[] s = new string[arr.Count];
			for (int i = 0; i < arr.Count; i++)
				s[i] = (string)arr[i];
			dataReader.Close();
			return s;
		}

		public string[] GetAllTableNameOrderByName()
		{
			string scmd = "select * from sys.tables order by name";
			dataReader = this.Exec(scmd);

			ArrayList arr = new ArrayList();
			while (dataReader.Read())
				arr.Add(dataReader.GetValue(0).ToString());

			string[] s = new string[arr.Count];
			for (int i = 0; i < arr.Count; i++)
				s[i] = (string)arr[i];
			dataReader.Close();
			return s;
		}

		//Lấy tên các cột trong table
		public string[] GetAllColumnsName(string tableName)
		{
			string scmd = "SELECT [Name] FROM syscolumns WHERE id=OBJECT_ID(N'" + tableName + "') ORDER BY colid";
			dataReader = this.Exec(scmd);

			ArrayList arr = new ArrayList();
			while (dataReader.Read())
				arr.Add(dataReader.GetValue(0).ToString());

			string[] s = new string[arr.Count];
			for (int i = 0; i < arr.Count; i++)
				s[i] = (string)arr[i];
			dataReader.Close();
			return s;
		}

		//Lấy PK của table
		public string GetPrimaryKey(string tableName)
		{
			string pk = null;
			string scmd = "select c.COLUMN_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk, INFORMATION_SCHEMA.KEY_COLUMN_USAGE c where pk.TABLE_NAME = '" + tableName + "' and	CONSTRAINT_TYPE = 'PRIMARY KEY' and	c.TABLE_NAME = pk.TABLE_NAME and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME";
			dataReader = this.Exec(scmd);
			try
			{
				if (dataReader.Read())
					pk = dataReader.GetValue(0).ToString();
			}
			catch
			{
				pk = null;
			}
			dataReader.Close();
			return pk;
		}

		//Lấy kiểu dữ liệu các cột trong table
		public string[] GetAllColumnsDataType(string tableName, int length)
		{
			string scmd = "SELECT [Type] FROM syscolumns WHERE id=OBJECT_ID(N'" + tableName + "') ORDER BY colid";
			dataReader = this.Exec(scmd);

			string[] s = new string[length];
			int i = 0;
			string typeName = "";
			while (dataReader.Read())
			{
				switch (dataReader.GetValue(0).ToString())
				{
					case "34":
						typeName = "image";
						break;
					case "35":
					case "39":
					case "47":
					case "155":
					case "135":
					case "174":
						typeName = "string";
						break;
					case "50":
						typeName = "bool";
						break;
					case "49":
					case "51":
					case "58":
					case "61":
					case "111":
					case "123":
					case "147":
						typeName = "DateTime";
						break;
					case "106":
					case "109":
					case "62":
						typeName = "float";
						break;
					default:
						typeName = "int";
						break;
				}
				s[i] = typeName;
				i++;
			}
			dataReader.Close();
			return s;
		}

		//Lấy description cua 1 cot
		public string[] GetAllColumnsDescription(string tableName, string[] columnsName)
		{
			string scmd = "SELECT * FROM fn_listextendedproperty(NULL, 'SCHEMA', 'dbo', 'TABLE','" + tableName + "','COLUMN','";
			string[] s = new string[columnsName.Length];
			for (int i = 0; i < columnsName.Length; i++)
			{
				string scmdFull = scmd + columnsName[i] + "')";
				dataReader = this.Exec(scmdFull);
				while (dataReader.Read())
				{
					s[i] = dataReader.GetValue(3).ToString(); //3:desc
				}
			}
			dataReader.Close();
			return s;
		}

		public string GetColumnDescription(string tableName, string columnName)
		{
			string scmd = "SELECT value FROM fn_listextendedproperty(NULL, 'SCHEMA', 'dbo', 'TABLE','" + tableName + "','COLUMN','" + columnName + "')";
			string des = null;
			SqlConnection cnn;
			cnn = new SqlConnection(strcon);
			cnn.Open();
			SqlCommand sqlComm = new SqlCommand(scmd, cnn);
			SqlDataReader dataReader = sqlComm.ExecuteReader();
			try
			{
				if (dataReader != null)
					if (dataReader.HasRows)
						if (dataReader.Read())
							des = (string)dataReader["value"];
			}
			catch
			{
			}
			cnn.Close();
			if (des == null)
				des = columnName;
			else
				if (des == "")
					des = columnName;
			return des;
		}

		public bool ChkColumnAutoID(string tableName, string columnName)
		{
			string scmd = "SELECT status FROM syscolumns WHERE id=OBJECT_ID(N'" + tableName + "') AND name = '" + columnName + "' ORDER BY colid";
			bool isAutoID = false;
			SqlConnection cnn;
			cnn = new SqlConnection(strcon);
			cnn.Open();
			SqlCommand sqlComm = new SqlCommand(scmd, cnn);
			SqlDataReader dataReader = sqlComm.ExecuteReader();
			try
			{
				if (dataReader != null)
					if (dataReader.HasRows)
						if (dataReader.Read())
							isAutoID = int.Parse(dataReader.GetValue(0).ToString()) == 128;
			}
			catch (Exception ex)
			{

			}
			cnn.Close();
			return isAutoID;
		}

		private object AnalyseDefaultValue(string defaultValue, string dataType)
		{
			object value = null;
			string data = "";
			if (defaultValue != null)
			{
				switch (dataType)
				{
					case "bit":
						data = defaultValue.Substring(2, 1);
						value = data == "1" ? true : false;
						break;
					case "char":
					case "text":
					case "varchar":
						data = defaultValue.Replace("('", "");
						data = data.Replace("')", "");
						value = data;
						break;
					case "datetime":
					case "smalldatetime":
					case "image":
					case "sql_variant":
					case "timestamp":
					case "uniqueidentifier":
					case "varbinary":
					case "xml":
						break;
					case "decimal":
					case "numeric":
						data = defaultValue.Replace("((", "");
						data = data.Replace("))", "");
						value = decimal.Parse(data);
						break;
					case "float":
					case "real":
						data = defaultValue.Replace("((", "");
						data = data.Replace("))", "");
						value = float.Parse(data);
						break;
					case "bigint":
					case "int":
					case "smallint":
					case "tiyint":
						data = defaultValue.Replace("((", "");
						data = data.Replace("))", "");
						value = int.Parse(data);
						break;
					case "money":
					case "smallmoney":
						data = defaultValue.Replace("((", "");
						data = data.Replace("))", "");
						value = double.Parse(data);
						break;
					case "nchar":
					case "ntext":
					case "nvarchar":
						data = defaultValue.Replace("(N'", "");
						data = data.Replace("')", "");
						value = data;
						break;
				}
			}
			return value;
		}

		private string convert2VariantType(string dataType)
		{
			string result = "";
			switch (dataType)
			{
				case "bigint":
					result = "Int64";
					break;
				case "binary":
				case "image":
				case "varbinary":
					result = "byte[]";
					break;
				case "bit":
					result = "bool";
					break;
				case "char":
				case "nchar":
				case "ntext":
				case "nvarchar":
				case "text":
				case "varchar":
					result = "string";
					break;
				case "datetime":
				case "smalldatetime":
				case "timestamp":
					result = "DateTime";
					break;
				case "decimal":
				case "numeric":
					result = "decimal";
					break;
				case "float":
				case "real":
					result = "double";
					break;
				case "int":
					result = "int";
					break;
				case "money":
				case "smallmoney":
					result = "double";
					break;
				case "smallint":
				case "tinyint":
					result = "Int16";
					break;
				case "sql_variant":
				case "xml":
				case "uniqueidentifier":
					result = "object";
					break;
			}
			return result;
		}

		private string convert2SqlDataType(string dataType)
		{
			string result = "";
			switch (dataType)
			{
				case "bigint":
					result = "BigInt";
					break;
				case "binary":
					result = "Binary";
					break;
				case "image":
					result = "Image";
					break;
				case "varbinary":
					result = "VarBinary";
					break;
				case "bit":
					result = "Bit";
					break;
				case "char":
					result = "Char";
					break;
				case "nchar":
					result = "NChar";
					break;
				case "ntext":
					result = "NText";
					break;
				case "nvarchar":
					result = "NVarChar";
					break;
				case "text":
					result = "Text";
					break;
				case "varchar":
					result = "VarChar";
					break;
				case "datetime":
					result = "DateTime";
					break;
				case "smalldatetime":
					result = "SmallDateTime";
					break;
				case "timestamp":
					result = "Timestamp";
					break;
				case "decimal":
				case "numeric":
					result = "Decimal";
					break;
				case "float":
					result = "Float";
					break;
				case "real":
					result = "Real";
					break;
				case "int":
					result = "Int";
					break;
				case "money":
					result = "Money";
					break;
				case "smallmoney":
					result = "SmallMoney";
					break;
				case "smallint":
					result = "SmallInt";
					break;
				case "tinyint":
					result = "TinyInt";
					break;
				case "sql_variant":
					result = "Variant";
					break;
				case "xml":
					result = "Xml";
					break;
				case "uniqueidentifier":
					result = "UniqueIdentifier";
					break;
			}
			return result;
		}
	}
}
